package Util::Store::StoreSearch;
use Moose;
use namespace::autoclean;
use JSON;

has 'schema' => (
	is => 'rw',
	required => 1,
	isa => 'DBIx::Class::Schema',
);

sub get_new_ten {
	my ($self) = @_;
	my @stores = $self->schema->resultset('Store')->search(
		{},
		{
			order_by => {-desc => [qw/created/]},
			rows => 10
		}
	);
	return \@stores;
}
sub store_search {
	my ($self, $page) = @_;
	unless(defined($page)) {
		return $self->schema->resultset('Store')->search();
	} else {
		return $self->schema->resultset('Store')->search(
			{},
			{
				order_by => {-desc => [qw/created/]},
				rows => 20,
				page => $page
			}
		);
	}
}

sub get_unpicked_by_nick{
	my ($self, $nick) = @_;
	return $self->schema->resultset('Store')->search([
			{ owner_nick => $nick, picked => 'R'},
			{ owner_nick => $nick, picked => 'Y'}
			],
			{}
			);
}

sub get_unpicked_by_email{
	my ($self, $email) = @_;
	return $self->schema->resultset('Store')->search(
			{ owner_email => $email, picked => 'Y'},
			{}
			);
}

sub get_stores_by_numiid{
	my ($self, $numiid) = @_;
	$numiid =~ s/^\s+//;
        $numiid =~ s/\s+$//;
        $numiid = "%" . $numiid. "%";
	return $self->schema->resultset('Store')->search(
			{url => {like => $numiid}},
			{}
			);
}

sub find {
	my ($self, $store_id) = @_;
	return $self->schema->resultset('Store')->find($store_id);

}

sub find_stores_buy_by{
	my ($self, $user_id, $page) = @_;
	my @stores;
	@stores = $self->schema->resultset('Store')->search(
		{
			'user_stores.user_id' => $user_id,
			'user_stores.role' => 'T',	
		},
		{
			select => [
				'id',
				'name',
				'image',
				{count => 'user_stores_2.user_stores_id', -as => 'people_count'}
			],
			as => [qw/
			id
                        name 
                       	image
			people_count 
                        /],
			order_by=>{-desc=>"id"},
			group_by => [qw/id/],
			page=>$page,
			join=> ['user_stores', 'user_stores']
		}
	);
	return \@stores;
}

sub find_stores_buy_by_status{
	my ($self, $user_id, $page) = @_;
	my @stores;
	@stores = $self->schema->resultset('Store')->search(
		{
			'user_stores.user_id' => $user_id,
			'user_stores.role' => 'T',	
			'user_stores.notify_status' => 'N',
		},
		{
			select => [
				'id',
				'name',
				'image',
				#{count => 'user_stores_2.user_stores_id', -as => 'people_count'}
				{sum => ["CASE WHEN user_stores_2.role = 'T' THEN CASE WHEN user_stores_2.notify_status = 'N' THEN 1 ELSE 0 END ELSE 0 END"], -as => 'people_count'}
			],
			as => [qw/
			id
                        name 
                       	image
			people_count 
                        /],
			order_by=>{-desc=>"id"},
			group_by => [qw/id/],
			page=>$page,
			join=> ['user_stores','user_stores']
		}
	);
	return \@stores;
}

sub find_stores_buy_by_count{
	my ($self, $user_id) = @_;
	return $self->schema->resultset('Store')->search(
		{
			'user_stores.user_id' => $user_id,
			'user_stores.role' => 'T'
		},
		{
			join=> ['user_stores']
		}
	)->count();
}

sub find_stores_by_owner{
	my ($self, $user_id, $page) = @_;
	my @stores;
	@stores = $self->schema->resultset('Store')->search(
		{
			'user_stores.user_id' => $user_id,
			'user_stores.role' => 'C'
		},
		{
			select => [
				'id',
				'name',
				'image',
				{sum => ["CASE WHEN user_stores_2.role = 'T' THEN CASE WHEN user_stores_2.notify_status = 'N' THEN 1 ELSE 0 END ELSE 0 END"], -as => 'people_count'}
			],
			as => [qw/
			id
                        name 
                       	image
			people_count 
                        /],
			order_by=>{-desc=>"people_count"},
			group_by => [qw/id/],
			page=>$page,
			join=> ['user_stores', 'user_stores']
		}
	);
	return \@stores;
}

sub find_stores_by_owner_count{
	my ($self, $user_id) = @_;
	return $self->schema->resultset('Store')->search(
		{
			'user_stores.user_id' => $user_id,
			'user_stores.role' => 'C'
		},
		{
			join=> ['user_stores']
		}
	)->count();
}

sub store_paging {
	my ($self, $name, $category, $page) = @_;

	$name =~ s/^\s+//;
        $name =~ s/\s+$//;
        $name = "%" . $name . "%";

	my $rows = 30;
	my @stores;
	if ($name eq '%%' and $category eq '0') {
                @stores = $self->schema->resultset('Store')->search(
				{},
				{
					order_by=>{-desc=>"id"},
					page=>$page,
					rows => $rows
				});

        } elsif($name eq '%%' and $category ne '0'){
                @stores = $self->schema->resultset('Store')->search(
				{
					'stores_category.category_id' => $category},
				{
					order_by=>{-desc=>"id"},
					page=>$page,
					rows => $rows,
					join=> 'stores_category'
				});
        } elsif($name ne '%%' and $category eq '0'){
                @stores = $self->schema->resultset('Store')->search(
				{
					name => {like => $name}},
				{
					order_by=>{-desc=>"id"},
					rows => $rows,
					page=>$page
				}
				);
        } else {
                @stores = $self->schema->resultset('Store')->search(
				{
					'me.name' => {like => $name},
					'stores_category.category_id' => $category},
				{
					order_by=>{-desc=>"id"},
					page=>$page,
					rows => $rows,
					join => 'stores_category'
				});
        }
	return @stores;
}

sub get_names_in_json {
        my ($self, $name) = @_;
	$name =~ s/^\s+//;
	$name =~ s/\s+$//;
        $name = "%" . $name . "%";
        my @arr = $self->schema->resultset('Store')->search({name => { like => $name}}, {columns => [qw/name/]});
        my %result;
        my $i = 0;
        foreach (@arr){
                my $value = $_->get_column("name");
                $value =~ s/^\s+//;
                $value =~ s/\s+$//;
                $result{$value} = 1 unless (exists $result{$value});
        }
        my @keys = keys %result;
        return to_json(\@keys);
}

sub store_total {
	my ($self, $name, $category) = @_;

	$name =~ s/^\s+//;
	$name =~ s/\s+$//;
	$name = "%" . $name . "%";
        if ($name eq '%%' and $category eq '0') {
                return $self->schema->resultset('Store')->search()->count;
        } elsif($name eq '%%' and $category ne '0'){
                return $self->schema->resultset('Store')->search(
				{'stores_category.category_id' => $category},
				{join => 'stores_category'}
			)->count;
        } elsif($name ne '%%' and $category eq '0'){
                return $self->schema->resultset('Store')->search(
				{ name => { like => $name}},
				{}
			)->count;
        } else {
                return $self->schema->resultset('Store')->search(
				{ 'me.name' => { like => $name},
				'stores_category.category_id' => $category},
				{join => 'stores_category'}
			)->count;
        }
}

sub get_shenpi_tuans {
	my ($self, $store_id) = @_;

        my @tuans = $self->schema->resultset('Tuan')->search(
                {
                        'tuan_stores.stores_id' => $store_id,
			'me.status' => 'A',
                },
                {
                        join => 'tuan_stores'
                }
        );
	return \@tuans;
}

sub get_ongoing_tuans {
	my ($self, $store_id) = @_;

	my $curr_time = `date --iso-8601=seconds`;
        my @tuans = $self->schema->resultset('Tuan')->search(
                {
                        'tuan_stores.stores_id' => $store_id,
			'me.status' => 'Y',
			'me.tihuo_time_to' => {">" => "$curr_time"},
                },
                {
                        join => 'tuan_stores'
                }
        );
	return \@tuans;

}

sub get_outofdates_tuans {
	my ($self, $store_id) = @_;

	my $curr_time = `date --iso-8601=seconds`;
        my @tuans = $self->schema->resultset('Tuan')->search(
                {
                        'tuan_stores.stores_id' => $store_id,
			'me.status' => 'Y',
			'me.tihuo_time_to' => {"<" => "$curr_time"},
                },
                {
			'+select'=> [
				{sum => 'user_tuan_stores.amount', -as => 'total_sold_count'}
			],
			order_by => {-desc => [qw/total_sold_count/]},
			group_by => 'me.id',
                        join => ['tuan_stores', 'user_tuan_stores']
                }
        );
	return \@tuans;

}

__PACKAGE__->meta->make_immutable;

1;
